-- -------------------------------------------------
--    Project Name:  平台中断-订阅维度明细数据
--    File Name:     jms_dm.dm_cn_breakoff_detail_reading_dt
--    Description:   计算（紫金山、逍遥峰）中心-中心、中心-集散、中心-网点超过24、48、72、96、120小时数据
--    Author:        Joyce | 利玉仙
--    date:          2021-12-02
-- -------------------------------------------------
with t_taking as (
    select *
    from (
             select billcode
                  , scansitecode
                  , scantime
                  , row_number() over (partition by billcode order by scantime asc ) rnk
             from jms_ods.tab_barscan_taking
             where dt between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
               and billcode like 'JT%'
         ) a
    where a.rnk = 1
      and date_format(scantime, 'yyyy-MM-dd') between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and '{{ execution_date | date_add(-1) | cst_ds }}'
),
     t_order_source as (
         select *
         from (
                  SELECT
                        waybill_no AS billcode,
                        input_time as create_time,
                        ordersource_code AS order_source
                  FROM jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                  WHERE dt BETWEEN date_add('{{ execution_date | date_add(-1) | cst_ds }}', -15) and date_add('{{ execution_date | date_add(-1) | cst_ds }}', 1)
                    and ordersource_code IN ('D67', 'D190')
              ) a
         where date_format(a.create_time, 'yyyy-MM-dd') between date_add('{{ execution_date | date_add(-1) | cst_ds }}', -8) and '{{ execution_date | date_add(-1) | cst_ds }}'
     )
insert overwrite table jms_dm.dm_cn_breakoff_detail_reading_dt partition ( dt )
select t_order_source.billcode
     , t_taking.scantime                                      as taking_time
     , t_order_source.create_time                             as read_time
     , t_break.network_code
     , t_break.network_name
     , t_break.network_type
     , t_break.agent_code
     , t_break.agent_name
     , t_break.franchisee_code
     , t_break.franchisee_name
     , if(t_order_source.order_source = 'D67', 2, 3)         as order_source_code
     , if(t_order_source.order_source = 'D67', '紫金山', '逍遥峰') as order_source_name
     , t_break.breakoff_type
     , t_break.arrival_count
     , t_break.send_count
     , t_break.send_shipment
     , t_break.actual_departure_time
     , t_break.planned_departure_time
     , t_break.next_actual_arrival_time
     , t_break.next_planned_arrival_time
     , t_break.actual_trans_min
     , t_break.planned_trans_min
     , t_break.delay_trans_min
     , t_break.stop_min
     , t_break.stop_over_12
     , t_break.customer_code
     , t_break.customer_name
     , t_break.breakoff_scan_type
     , t_break.breakoff_scan_time
     , t_break.next_scan_code
     , t_break.next_scan_name
     , t_break.next_scan_type
     , t_break.next_agent_code
     , t_break.next_agent_name
     , t_break.next_franchisee_code
     , t_break.next_franchisee_name
     , t_break.next_est_scantype
     , t_break.next_est_scantime
     , t_break.end_code
     , t_break.end_name
     , t_break.breakoff_min
     , t_break.mage_region_code
     , t_break.mage_region_name
     , date_format(t_order_source.create_time, 'yyyy-MM-dd')  as dt
from t_order_source t_order_source
         join jms_dm.dm_cn_breakoff_detail_total_dt t_break
              on t_order_source.billcode = t_break.billcode
         left join t_taking t_taking
                   on t_order_source.billcode = t_taking.billcode
where t_break.dt = '{{ execution_date | date_add(-1) | cst_ds }}'
    
 distribute by pmod(hash(rand()), 21);